﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Xml;
using System.Xml.Linq;

using Model;
using Interface;

namespace Controller
{
    class DBFacade
    {
        ProductCol productCol;
        BrandCol brandCol;
        CategoryCol categoryCol;
        ProductDetailCol productDetailCol;
        XMLParser xmlParser;
        DBHelper db;
        Category category;
        Brand brand;
        Product product;
        ProductDetail productDetail;
        SalesOrganization salesOrganization;
        SalesOrganizationCol salesOrganizationCol;

        public DBFacade(ProductCol productCollection, BrandCol brandCollection, CategoryCol categoryCollection, ProductDetailCol productDetailCollection, SalesOrganizationCol salesOrganizationCollection)
        {
            this.productCol = productCollection;
            this.brandCol = brandCollection;
            this.categoryCol = categoryCollection;
            this.productDetailCol = productDetailCollection;
            this.salesOrganizationCol = salesOrganizationCollection;
            xmlParser = new XMLParser();

            // Connect til databasen
            db = new DBHelper("ealdb1.eal.local", "ejl05_usr", "Baz1nga5");
            db.Connect();
        }

        #region Load data

        public void LoadData()
        {
            // Load existing data from DB into model:
            InsertBrandToModel();
            InsertCategoryToModel();
            InsertProductToModel();
            InsertProductDetailToModel();
            InsertSalesOrganizationToModel();

            // First of all, the program ought to check whether 

            //LoadBrand();

            //LoadCategory();

            LoadProduct("xml/TestSet1_products_20130221_153004.xml");
        }

        public void LoadBrand()
        {
            Console.WriteLine("LOADING BRANDS...");

            // Parse 
            xmlParser.Parse("xml/TestSet1_brands_20130221_153004.xml", "Brands");

            // Tjek hvor mange objekter den returnerede liste indeholder:
            Console.WriteLine("Amount of brands: {0}", xmlParser.Brands.Count);

            // Loop igennem listen og test, om de bliver udskrevet korrekt (disse data skal indsættes i databasen):
            for (int i = 0; i < xmlParser.Brands.Count; i++)
            {
                int brandId = xmlParser.Brands[i].Id;
                string brandName = xmlParser.Brands[i].BrandName;

                // Læg i DB (DE ER LAGT I DB)
                //this.db.Open();
                //if (this.db.InsertRow("Brand", "Id, Name", "'" + brandId + "', '" + brandName + "'"))
                //    Console.WriteLine("({0}): {1} blev lagt i DB", brandId, brandName);
                //this.db.Close();
            }
        }

        public void LoadCategory()
        {
            Console.WriteLine("LOADING CATEGORIES...");

            // Parse kategorier:
            xmlParser.Parse("xml/TestSet1_categories_20130221_153004.xml", "Categories");

            // Udskriv antal kategorier:
            Console.WriteLine("\nLOADING CATEGORIES...");
            Console.WriteLine("Amount of categories: {0}", xmlParser.Categories.Count);

            // Loop igennem kategorier og udskriv dem:
            for (int i = 0; i < xmlParser.Categories.Count; i++)
            {
                int categoryId = xmlParser.Categories[i].Id;
                string categoryName = xmlParser.Categories[i].Name;
                string categoryDesc = xmlParser.Categories[i].Description;

                // Læg i DB (DE ER LAGT I DB)
                //this.db.Open();
                //if (this.db.InsertRow("Category", "Id, Name, Description", "'" + categoryId + "', '" + categoryName + "', '" + categoryDesc + "'"))
                //    Console.WriteLine("({0}): {1} - {2} blev lagt i DB", xmlParser.Categories[i].Id, xmlParser.Categories[i].Name, xmlParser.Categories[i].Description);
                //this.db.Close();
            }
        }

        public void LoadProduct(string xmlPath)
        {
            this.db.Open();
            Console.WriteLine("Checking versions");

            // Find latest version:
            int latest = salesOrganizationCol.SOList.Max(s => s.Id);
            string[] createdTime = new string[2];
            createdTime = salesOrganizationCol.SOList[(latest - 1)].CreatedTime.Split('T');

            if (DateTime.Parse(xmlParser.CheckVersion(xmlPath)).Ticks > DateTime.Parse(createdTime[0]).Ticks)
            {
                // The new XML file is newer:
                xmlParser.Parse(xmlPath, "Products");

                Console.WriteLine("Amount of products: {0}", xmlParser.SalesOrganization.Count);
                Console.WriteLine("Id: {0}\nCreated Time: {1}\n", xmlParser.SalesOrganization[0].Id, xmlParser.SalesOrganization[0].CreatedTime);
                for (int i = 0; i < xmlParser.SalesOrganization[0].Products.Count; ++i)
                {
                    Console.WriteLine("Id: {0}", xmlParser.SalesOrganization[0].Products[i].Id);
                    for (int j = 0; j < xmlParser.SalesOrganization[0].Products[i].ProductDetails.Count; ++j)
                    {
                        Console.WriteLine("Description: {0}", xmlParser.SalesOrganization[0].Products[i].ProductDetails[j].Description);
                        Console.WriteLine("Language: {0}", xmlParser.SalesOrganization[0].Products[i].ProductDetails[j].Language);
                    }
                }

                // Insert DB stuff here, that uploads data to the DB (preferably sqlbulkcopy - because its blazin'!)
            }
            else
                Console.WriteLine("The system is up to date.");

            this.db.Close();
        }

        #endregion

        public Brand FindBrand(int brandcode)
        {
            int max = brandCol.BrandList.Count;
            int min = 1;

            while (max >= min)
            {
                int mid = (min + max) / 2;

                if (brandCol.BrandList[mid].Id < brandcode)
                    min = mid + 1;
                else if (brandCol.BrandList[mid].Id > brandcode)
                    max = mid - 1;
                else
                    return brandCol.BrandList[mid];
            }
            
            return null;
        }

        // Find kategori
        public Category FindCategory(int id)
        {
            int max = categoryCol.CategoryList.Count;
            int min = 1;

            while (max >= min)
            {
                int mid = (min + max) / 2;

                if (categoryCol.CategoryList[mid].Id < id)
                    min = mid + 1;
                else if (categoryCol.CategoryList[mid].Id > id)
                    max = mid - 1;
                else
                    return categoryCol.CategoryList[mid];
            }

            return null;
        }
        public Product FindProduct(int id)
        {
            int max = productCol.GetProductListe().Count;
            int min = 0;

            while (max >= min)
            {
                int mid = (min + max) / 2;

                if (productCol.GetProductListe()[mid].Id < id)
                    min = mid + 1;
                else if (productCol.GetProductListe()[mid].Id > id)
                    max = mid - 1;
                else
                    return productCol.GetProductListe()[mid];
            }

            return null;
        }

        // Insert data to model from DB:
        public void InsertProductToModel()
        {
            this.db.Open();

            this.db.SelectDataSP("Getproducts");
            while (db.data.Read())
            {
                int id = (int)db.data["Id"];
                Brand brand = FindBrand((int)db.data["BrandId"]);
                Category category = FindCategory((int)db.data["CategoryId"]);
                string name = db.data["Name"].ToString();
                string available = db.data["AvailabilityLevel"].ToString();
                string status = db.data["Status"].ToString();
                string launchDate = db.data["LaunchDate"].ToString();
                decimal price = (decimal)db.data["Price"];

                product = new Product(id, status, launchDate, available, price, category, brand);
                productCol.AddProduct(product);
            }

            this.db.Close();
        }
        public void InsertBrandToModel()
        {
            this.db.Open();
            this.db.SelectDataSP("GetBrands");
            
            brand = new Brand(0, "Vis alle");
            brandCol.AddBrand(brand);

            while (db.data.Read())
            {
                int id = (int)db.data["Id"];
                string brandname = db.data["Name"].ToString();

                brand = new Brand(id, brandname);
                brandCol.AddBrand(brand);
            }
            this.db.Close();
        }
        public void InsertCategoryToModel()
        {
            this.db.Open();
            this.db.SelectDataSP("Getcategories");

            category = new Category(0, "Vis alle", string.Empty);
            categoryCol.AddCategory(category);

            while (db.data.Read())
            {
                int id = (int)db.data["Id"];
                string name = db.data["Name"].ToString();
                string description = db.data["Description"].ToString();

                category = new Category(id, name, description);
                categoryCol.AddCategory(category);
            }
            this.db.Close();
        }
        public void InsertProductDetailToModel()
        {
            this.db.Open();
            this.db.SelectDataSP("GetDescriptions");

            while (db.data.Read())
            {
                Product product = FindProduct((int)db.data["ProductId"]);
                string language = db.data["Language"].ToString();
                string description = db.data["Description"].ToString();
                string notes = db.data["Notes"].ToString();

                productDetail = new ProductDetail(product, language, description, notes);
                productDetailCol.AddProductDetail(productDetail);
            }
            this.db.Close();
        }
        public void InsertSalesOrganizationToModel()
        {
            this.db.Open();
            this.db.SelectDataSP("GetsalesOrganization");
            while (db.data.Read())
            {
                int id = (int)db.data["Id"];
                string createdTime = db.data["CreatedTime"].ToString();
                int numberOfProducts = (int)db.data["NumberOfProducts"];
                string currency = db.data["Currency"].ToString();

                salesOrganization = new SalesOrganization(id, createdTime, numberOfProducts, currency);
                salesOrganizationCol.AddSO(salesOrganization);
            }
            this.db.Close();
        }
    }
}